/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import DTO.DTOProduct;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

/**
 *
 * @author Pham
 */
public class DALProduct {
    ConnectionDB conn;
    
    public DALProduct()
    {
        conn = new ConnectionDB();
    }
    
    //get all product
    public ArrayList<DTOProduct> getProducts()
    {
        try
        {
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String queryString = "select * from product";
            ResultSet rs = conn.executeQuery(queryString);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //search product
    public ArrayList<DTOProduct> searchProducts(String name, String manu, long priceFrom, long priceTo)
    {
        try
        {
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String queryString = "select * from manufacture m, product p where p.manufacture_id = m.manufacture_id ";
            if(!"".equals(name))
                queryString += " and p.product_name like '%"+ name +"%'";
            if(!"Tất cả các hãng".equals(manu))
                queryString += " and m.manufacture_name = '"+ manu +"'";
            if(priceFrom >= 0)
                queryString += " and p.product_price >= " + priceFrom;
            if(priceTo > 0)
                queryString += " and p.product_price <= " + priceTo;
            ResultSet rs = conn.executeQuery(queryString);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
            
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //get product by ID
    public DTOProduct getProductbyID(int ID)
    {
        try
        {
            DTOProduct dtoPro = null;
            String queryString = "select * from product where product_id = " + ID;
            ResultSet rs = conn.executeQuery(queryString);
            while(rs.next())
            {
                dtoPro = new DTOProduct(rs);
            }
            return dtoPro;
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
    
    //method insert new row to table product
    public boolean insertRowProduct(DTOProduct dtoPro)
    {
        try{
            DateFormat dateF = new SimpleDateFormat("yyyy-MM-dd");
            String datePro = dateF.format(dtoPro.getProductDateUpdate());
            String insertString = String.format("insert into product(product_name, product_quantity, product_price, product_image, "
                    + "product_dateupdate, product_description, product_warranty, product_promotion, manufacture_id) "
                    + "values(N'%s', %d, %d, N'%s', '%s', N'%s', N'%s', N'%s', %d)", dtoPro.getProductName(), dtoPro.getProductQuantity(),
                    dtoPro.getProductPrice(), dtoPro.getProductImage(), datePro, dtoPro.getProductDescription(),
                    dtoPro.getProductWarranty(), dtoPro.getProductPromotion(), dtoPro.getManu().getManuID());
//            System.out.print(insertString);
            if(conn.exeUpdate(insertString))
                return true;
            return false;
        }
        catch(Exception ex)
        {
            System.err.print(ex.getMessage());
            return false;
        }
    }
    
    //method update product
    public boolean updateProduct(DTOProduct dtoPro)
    {
        try
        {
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            String datePro = df.format(dtoPro.getProductDateUpdate());
            String queryUpdate;
            if(dtoPro.getProductQuantity() > 0){
                queryUpdate = String.format("update product set product_name = N'%s',"
                    + " product_price = %d,"
                    + " product_quantity = %d,"
                    + " product_warranty = N'%s',"
                    + " product_description = N'%s',"
                    + " product_promotion = N'%s',"
                    + " product_image = '%s',"
                    + " product_dateupdate = '%s',"
                    + " manufacture_id = %d where product_id = %d", dtoPro.getProductName(), dtoPro.getProductPrice(),
                    dtoPro.getProductQuantity(), dtoPro.getProductWarranty(), dtoPro.getProductDescription(), dtoPro.getProductPromotion(),
                    dtoPro.getProductImage(), datePro, dtoPro.getManu().getManuID(), dtoPro.getProductID());
            }
            else
                queryUpdate = String.format("update product set product_name = N'%s',"
                        + " product_price = %d,"
                        + " product_warranty = N'%s',"
                        + " product_description = N'%s',"
                        + " product_promotion = N'%s',"
                        + " product_image = '%s',"
                        + " product_dateupdate = '%s',"
                        + " manufacture_id = %d where product_id = %d", dtoPro.getProductName(), dtoPro.getProductPrice(),
                        dtoPro.getProductWarranty(), dtoPro.getProductDescription(), dtoPro.getProductPromotion(),
                        dtoPro.getProductImage(), datePro, dtoPro.getManu().getManuID(), dtoPro.getProductID());
            //System.out.printf(queryUpdate);
            if(conn.exeUpdate(queryUpdate))
                return true;
            return false;
        }
        catch(Exception ex)
        {
            System.err.println(ex.getMessage());
            return false;
        }
    }
    
    //method delete product
    public boolean deleteProduct(DTOProduct dtoPro)
    {
        try{
            String queryDel = "delete from product where product_id = " + dtoPro.getProductID();
            if(conn.exeUpdate(queryDel))
                return true;
            return false;
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return false;
        }
    }
    
    //method search product to import
    public ArrayList<DTOProduct> searchProductToImport(String name, String Manu, int quanFrom, int quanTo)
    {
        try
        {
            ArrayList<DTOProduct> listPro = new ArrayList<>();
            String query = "select * from product, manufacture where product.manufacture_id = manufacture.manufacture_id ";
            if(!"".equals(name))
                query += " and product_name like '%" + name +"%'";
            if(!"Tất cả các hãng".equals(Manu))
                query += "and manufacture_name = '"+ Manu +"'";
            if(quanFrom >= 0 && quanTo >= 0){
                //if(quanFrom < quanTo)
                    query += "and product_quantity between " +quanFrom+ " and " + quanTo + " ";
            }
            ResultSet rs = conn.executeQuery(query);
            while(rs.next())
            {
                listPro.add(new DTOProduct(rs));
            }
            return listPro;
            
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return null;
        }
    }
}
